package com.ztesoft.ossup.report.service;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import com.ztesoft.ossup.report.domain.ReportColumn;
import com.ztesoft.ossup.report.domain.ReportColumnGroup;

@Service("poiService")
public class PoiService {
	
	 public void downloadBak(HttpServletResponse response,String defaultFileName,List<ReportColumn> colList,List<Map<String, Object>> dataList) {  
	    
		 String fileName = defaultFileName + "_"+System.currentTimeMillis() +".xls";   
		 try {  
			 //outExcel(fileName, colList, dataList);//生成报表文件
			 
			 File file = new File(fileName);
				// 取得文件名。
				String filename = file.getName();
				// 以流的形式下载文件。
				InputStream fis = new BufferedInputStream(new FileInputStream(fileName));
				byte[] buffer = new byte[fis.available()];
				fis.read(buffer);
				fis.close();
				// 清空response
				response.reset();
				// 设置response的Header
				response.addHeader("Content-Disposition", "attachment;filename="
						+ new String(filename.getBytes()));
				response.addHeader("Content-Length", "" + file.length());
				OutputStream toClient = new BufferedOutputStream(
						response.getOutputStream());
				response.setContentType("application/vnd.ms-excel;charset=gb2312");
				toClient.write(buffer);
				toClient.flush();
				toClient.close();
	        } catch (Exception ex) {  
	            ex.printStackTrace();  
	        }  
	    } 
	 
	 public void download(HttpServletResponse response,String defaultFileName,List<ReportColumn> colList,List<Map<String, Object>> dataList, List<ReportColumnGroup> reportColumnGroupList) {  
		 String fileName = defaultFileName + "_"+System.currentTimeMillis() +".xls";  
		 OutputStream ouputStream = null;
		 try {  
			 Workbook wb = outExcel(fileName, colList, dataList,reportColumnGroupList);//生成报表文件
			 response.setContentType("application/vnd.ms-excel");  
		     response.setHeader("Content-disposition", "attachment;filename="+fileName);  
		     ouputStream = response.getOutputStream();  
		     wb.write(ouputStream);  
		      
	        } catch (Exception ex) {  
	            ex.printStackTrace();  
	        }finally{
	        	try {
					ouputStream.flush();
					ouputStream.close(); 
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}  
	        }  
	 }
	 public byte[] download(String defaultFileName,List<ReportColumn> colList,List<Map<String, Object>> dataList, List<ReportColumnGroup> reportColumnGroupList) {  
		 String fileName = defaultFileName + "_"+System.currentTimeMillis() +".xls";  
		 OutputStream ouputStream = null;
		 Workbook wb = null;
		 try {  
			 wb = outExcel(fileName, colList, dataList,reportColumnGroupList);//生成报表文件
//			 response.setContentType("application/vnd.ms-excel");  
//		     response.setHeader("Content-disposition", "attachment;filename="+fileName);  
//		     ouputStream = response.getOutputStream();  
//		     wb.write(ouputStream);  
		      
	        } catch (Exception ex) {  
	            ex.printStackTrace();  
	        }finally{
//	        	try {
//					ouputStream.flush();
//					ouputStream.close(); 
//				} catch (IOException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}  
	        } 
		 return ObjectToByte(wb);
	 }
	
	public Workbook outExcel(String fileName,List<ReportColumn> colList,List<Map<String, Object>> dataList,List<ReportColumnGroup> reportColumnGroupList){
		List<Map<String,String>> groupColsList = new ArrayList<Map<String,String>>();
		List<Map<String,String>> colsList = new ArrayList<Map<String,String>>();
		Map<String,String> colMap = null;
		Map<String,String> colGroupMap = null;
		List<ReportColumn> removeColList = new  ArrayList<ReportColumn>();
		if (colList != null && colList.size() >0){
			for (ReportColumn reportColumn:colList) {
				if (reportColumn.getIsHidden() == 1){
					removeColList.add(reportColumn);
				}
			}
			if (removeColList.size() > 0){
				for (ReportColumn reportColumn:removeColList) {
					colList.remove(reportColumn);
				}
			}
			for (ReportColumn reportColumn:colList) {
					colMap = new HashMap<String,String>();
					colMap.put("code", reportColumn.getCode());
					colMap.put("name", reportColumn.getName());
					colsList.add(colMap);
					if (reportColumnGroupList != null && reportColumnGroupList.size() >0){
						for (ReportColumnGroup reportColumnGroup : reportColumnGroupList){
							String dataCols = reportColumnGroup.getDataCols();
							dataCols = dataCols.replaceAll(",", "#");
							dataCols = "#"+ dataCols +"#";
							if (dataCols.indexOf("#"+reportColumn.getCode()+"#") > -1){
								colGroupMap = new HashMap<String,String>();
								colGroupMap.put("code", reportColumnGroup.getGroupField());
								colGroupMap.put("name", reportColumnGroup.getGroupName());
							}
						}
						if (colGroupMap ==null){
							groupColsList.add(colMap);
						}else{
							groupColsList.add(colGroupMap);
						}
						
					}
			}
		}
		
		
		Workbook wb = null;
		try{
			wb = new HSSFWorkbook();
			Sheet sheet = wb.createSheet("数据");
			Row row0 = sheet.createRow(0);
		    // Create header row
		    Cell cell = row0.createCell(0);
		    Row row1 = null; 
		    String nowCode ="";
		    String baseCode ="";
		    int colIndex = 0;
		    int spanIndex = 0;
		    if (reportColumnGroupList != null && reportColumnGroupList.size() > 0){
		    	row1 = sheet.createRow(1);
		    	for (int i = 0; i < groupColsList.size(); i++) {
		    		Map<String,String> groupColsMap = groupColsList.get(i);
		    		if (groupColsMap.get("code").equals(colsList.get(i).get("code"))){
		    			createHeaderCell(wb,row0,(short)i,groupColsMap.get("name"));
		    			sheet.addMergedRegion(new CellRangeAddress(0,1,i,i)); 
		    		}else {
		    			if (spanIndex > 0){
		    				if (groupColsMap.get("code").equals(baseCode)){
		    					spanIndex ++;
		    					if (i == groupColsList.size() -1){
		    						sheet.addMergedRegion(new CellRangeAddress(0,0,colIndex,i));
		    					}
		    				}else{
		    					sheet.addMergedRegion(new CellRangeAddress(0,0,colIndex,i-1)); 
		    					colIndex = i;
		    					baseCode = groupColsMap.get("code");
		    					spanIndex =  1;
		    					createHeaderCell(wb,row0,(short)i,groupColsMap.get("name"));
		    				}
		    			}else {
		    				createHeaderCell(wb,row0,(short)i,groupColsMap.get("name"));
		    				baseCode = groupColsMap.get("code");
		    				colIndex = i;
		    				spanIndex  = 1;
		    			}
		    		}
				}
		    }
		    for (int i = 0; i < colList.size(); i++) {
		    	ReportColumn reportColumn = colList.get(i);
			    	String colName = reportColumn.getName();
			    	 if (reportColumnGroupList != null && reportColumnGroupList.size() > 0){
			    		 createHeaderCell(wb,row1,(short)i,colName);
			    	 }else{
			    		 createHeaderCell(wb,row0,(short)i,colName);
			    	 }
			}
		    //create data row
		    //create CellStyle
		    CellStyle cellStyle = wb.createCellStyle();
	        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
	        
		    for (int j = 0; j < dataList.size(); j++) {
		    	Map dataMap = dataList.get(j);
		    	Row dataRow = sheet.createRow(j+2);
		    	for (int k = 0; k < colList.size(); k++) {
		    		ReportColumn reportColumn = colList.get(k);
		    			String colCode = reportColumn.getCode();
						String cellData = dataMap.get(colCode)==null?"":dataMap.get(colCode).toString();
						createDataCell(wb, dataRow, (short)k, cellData,cellStyle);
		    	}
		    }
//		    fileOut = new FileOutputStream(fileName);
//		    wb.write(fileOut);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
//			try {
//				fileOut.close();
//			} catch (IOException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
		}
		 return  wb;
	}
	
	//create header cell
	private void createHeaderCell(Workbook wb,Row headerRow,short cellIndex,String cellVal){
		
		CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//        cellStyle.setFillBackgroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
//        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //create font style
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short)14);
        cellStyle.setFont(font);
        
		Cell cell = headerRow.createCell(cellIndex);
	    cell.setCellValue(cellVal);
        
        cell.setCellStyle(cellStyle);
	}
	
	//create data cell
		private void createDataCell(Workbook wb,Row dataRow,short cellIndex,String cellVal,CellStyle cellStyle){
			
			Cell cell = dataRow.createCell(cellIndex);
		    cell.setCellValue(cellVal);
	        
	        cell.setCellStyle(cellStyle);
		}
		public byte[] ObjectToByte(java.lang.Object obj) {  
		    byte[] bytes = null;  
		    try {  
		        // object to bytearray  
		        ByteArrayOutputStream bo = new ByteArrayOutputStream();  
		        ObjectOutputStream oo = new ObjectOutputStream(bo);  
		        oo.writeObject(obj);  
		  
		        bytes = bo.toByteArray();  
		  
		        bo.close();  
		        oo.close();  
		    } catch (Exception e) {  
		        System.out.println("translation" + e.getMessage());  
		        e.printStackTrace();  
		    }  
		    return bytes;  
		} 
}
